// 从 node24 开始支持 sqlite 内置模块
import { DatabaseSync } from "node:sqlite";

// 连接数据库
const db = new DatabaseSync("./demo.db");

// 执行建表语句
db.exec(`
    CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(32) NOT NULL,
    email VARCHAR(64) NOT NULL,
    password VARCHAR(128) NOT NULL 
);`);
const sql = db.createTagStore();
// 执行插入语句
const result = sql.run`INSERT INTO Users (username, email, password) VALUES (${1}, ${2}, ${3})`;
console.log(result);
// 执行查询语句
const user1 = sql.get`SELECT * FROM Users WHERE id = ${result.lastInsertRowid}`;
console.log(user1);
// 执行更新语句
const result2 = sql.run`UPDATE Users SET username = ${'zhangsan'} WHERE id = ${1}`;
console.log(result2);
// 执行删除语句
const result3 = sql.run`DELETE FROM Users WHERE id = ${1}`;
console.log(result3);

// 关闭数据库连接
db.close();